<%@ page contentType="text/html; charset=UTF-8" language="java" %>
<%@ page import="com.base.myproject.server.tools.*" %>
<%@ page import="com.base.myproject.client.jdbc.DataSet,java.util.Date" %>
<%@page import="java.text.SimpleDateFormat,java.util.Calendar,java.math.BigDecimal"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html>
<head>
<title></title>
<LINK href="/hr_Jsp/report.css" rel=stylesheet>
<style type="text/css">
#40percentage {
	width: 100%;
	position: relative;
}
</style>
</head>
<body>
<%
	String year = request.getParameter("year");
	SimpleDateFormat format = new SimpleDateFormat("yyyy");
	if (year == null) {
		Date defaultDate = new Date();
		String date = format.format(defaultDate);
		year = date;
	}
	DataSetTool DST = new DataSetTool();
	String depSql = "select name,one,two,three,four,five,six,seven,eight,nine,ten,eleven,twelve,oneLeaveNo,oneSum,twoLeaveNo,twoSum,threeLeaveNo,threeSum,fourLeaveNo,fourSum,fiveLeaveNo,fiveSum,sixLeaveNo,sixSum,sevenLeaveNo,sevenSum,eightLeaveNo,eightSum,nineLeaveNo,nineSum,tenLeaveNo,tenSum,elevenLeaveNo,elevenSum,twelveLeaveNo,twelveSum " + 
						"from (select monthSum.systemid,(isnull(leavNo.num1,0)*1.0/(thisSum + onSum))*2 as one, isnull(leavNo.num1,0) oneLeaveNo, (thisSum + onSum) as oneSum from (select substring(systemid,1,4) as systemid,count(*) as onSum from bcode_job a inner join bcode b on b.bcode = a.bcode where isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-01') and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > CONVERT(varchar(7), dateadd(year, -1, '" + year + "' + '-12-01'), 120) and CONVERT(varchar(7), entrydate, 120) <= CONVERT(varchar(7), dateadd(year, -1, '" + year + "' + '-12-01'), 120) group by substring(systemid,1,4)) as monthSum inner join (select substring(systemid,1,4) as systemid,count(*) as thisSum from bcode_job a inner join bcode b on b.bcode = a.bcode where isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-01') and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + year + "' + '-01' and CONVERT(varchar(7), entrydate, 120) <= '" + year + "' + '-01' group by substring(systemid,1,4)) as thisMonthSum on thisMonthSum.systemid = monthSum.systemid left join (select substring(systemid,1,4) as systemid,count(*) as num1 from bcode_job a inner join bcode b on b.bcode = a.bcode where isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-01') and CONVERT(varchar(7), lastjobdate, 120) = '" + year + "' + '-01' group by substring(systemid,1,4)) as leavNo on leavNo.systemid = monthSum.systemid) as one " +
						"left join (select monthSum.systemid,(isnull(leavNo.num1,0)*1.0/(thisSum + onSum))*2 as two, isnull(leavNo.num1,0) twoLeaveNo, (thisSum + onSum) as twoSum from (select substring(systemid,1,4) as systemid,count(*) as onSum from bcode_job a inner join bcode b on b.bcode = a.bcode where isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-02') and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + year + "' + '-02' and CONVERT(varchar(7), entrydate, 120) <= '" + year + "' + '-02' group by substring(systemid,1,4)) as monthSum inner join (select substring(systemid,1,4) as systemid,count(*) as thisSum from bcode_job a inner join bcode b on b.bcode = a.bcode where isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-02') and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + year + "' + '-02' and CONVERT(varchar(7), entrydate, 120) <= '" + year + "' + '-02' group by substring(systemid,1,4)) as thisMonthSum on thisMonthSum.systemid = monthSum.systemid left join (select substring(systemid,1,4) as systemid,count(*) as num1 from bcode_job a inner join bcode b on b.bcode = a.bcode where isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-02') and CONVERT(varchar(7), lastjobdate, 120) = '" + year + "' + '-02' group by substring(systemid,1,4)) as leavNo on leavNo.systemid = monthSum.systemid) as two on two.systemid = one.systemid " + 
						"left join (select monthSum.systemid,(isnull(leavNo.num1,0)*1.0/(thisSum + onSum))*2 as three, isnull(leavNo.num1,0) threeLeaveNo, (thisSum + onSum) as threeSum from (select substring(systemid,1,4) as systemid,count(*) as onSum from bcode_job a inner join bcode b on b.bcode = a.bcode where isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-03') and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + year + "' + '-02' and CONVERT(varchar(7), entrydate, 120) <= '" + year + "' + '-02' group by substring(systemid,1,4)) as monthSum inner join (select substring(systemid,1,4) as systemid,count(*) as thisSum from bcode_job a inner join bcode b on b.bcode = a.bcode where isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-03') and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + year + "' + '-03' and CONVERT(varchar(7), entrydate, 120) <= '" + year + "' + '-03' group by substring(systemid,1,4)) as thisMonthSum on thisMonthSum.systemid = monthSum.systemid left join (select substring(systemid,1,4) as systemid,count(*) as num1 from bcode_job a inner join bcode b on b.bcode = a.bcode where isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-03') and CONVERT(varchar(7), lastjobdate, 120) = '" + year + "' + '-03' group by substring(systemid,1,4)) as leavNo on leavNo.systemid = monthSum.systemid) as three on three.systemid = one.systemid " + 
						"left join (select monthSum.systemid,(isnull(leavNo.num1,0)*1.0/(thisSum + onSum))*2 as four, isnull(leavNo.num1,0) fourLeaveNo, (thisSum + onSum) as fourSum from (select substring(systemid,1,4) as systemid,count(*) as onSum from bcode_job a inner join bcode b on b.bcode = a.bcode where isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-04') and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + year + "' + '-03' and CONVERT(varchar(7), entrydate, 120) <= '" + year + "' + '-03' group by substring(systemid,1,4)) as monthSum inner join (select substring(systemid,1,4) as systemid,count(*) as thisSum from bcode_job a inner join bcode b on b.bcode = a.bcode where isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-04') and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + year + "' + '-04' and CONVERT(varchar(7), entrydate, 120) <= '" + year + "' + '-04' group by substring(systemid,1,4)) as thisMonthSum on thisMonthSum.systemid = monthSum.systemid left join (select substring(systemid,1,4) as systemid,count(*) as num1 from bcode_job a inner join bcode b on b.bcode = a.bcode where isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-04') and CONVERT(varchar(7), lastjobdate, 120) = '" + year + "' + '-04' group by substring(systemid,1,4)) as leavNo on leavNo.systemid = monthSum.systemid) as four on four.systemid = one.systemid " +
						"left join (select monthSum.systemid,(isnull(leavNo.num1,0)*1.0/(thisSum + onSum))*2 as five, isnull(leavNo.num1,0) fiveLeaveNo, (thisSum + onSum) as fiveSum from (select substring(systemid,1,4) as systemid,count(*) as onSum from bcode_job a inner join bcode b on b.bcode = a.bcode where isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-05') and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + year + "' + '-04' and CONVERT(varchar(7), entrydate, 120) <= '" + year + "' + '-04' group by substring(systemid,1,4)) as monthSum inner join (select substring(systemid,1,4) as systemid,count(*) as thisSum from bcode_job a inner join bcode b on b.bcode = a.bcode where isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-05') and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + year + "' + '-05' and CONVERT(varchar(7), entrydate, 120) <= '" + year + "' + '-05' group by substring(systemid,1,4)) as thisMonthSum on thisMonthSum.systemid = monthSum.systemid left join (select substring(systemid,1,4) as systemid,count(*) as num1 from bcode_job a inner join bcode b on b.bcode = a.bcode where isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-05') and CONVERT(varchar(7), lastjobdate, 120) = '" + year + "' + '-05' group by substring(systemid,1,4)) as leavNo on leavNo.systemid = monthSum.systemid) as five on five.systemid = one.systemid " +
						"left join (select monthSum.systemid,(isnull(leavNo.num1,0)*1.0/(thisSum + onSum))*2 as six, isnull(leavNo.num1,0) sixLeaveNo, (thisSum + onSum) as sixSum from (select substring(systemid,1,4) as systemid,count(*) as onSum from bcode_job a inner join bcode b on b.bcode = a.bcode where isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-06') and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + year + "' + '-05' and CONVERT(varchar(7), entrydate, 120) <= '" + year + "' + '-05' group by substring(systemid,1,4)) as monthSum inner join (select substring(systemid,1,4) as systemid,count(*) as thisSum from bcode_job a inner join bcode b on b.bcode = a.bcode where isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-06') and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + year + "' + '-06' and CONVERT(varchar(7), entrydate, 120) <= '" + year + "' + '-06' group by substring(systemid,1,4)) as thisMonthSum on thisMonthSum.systemid = monthSum.systemid left join (select substring(systemid,1,4) as systemid,count(*) as num1 from bcode_job a inner join bcode b on b.bcode = a.bcode where isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-06') and CONVERT(varchar(7), lastjobdate, 120) = '" + year + "' + '-06' group by substring(systemid,1,4)) as leavNo on leavNo.systemid = monthSum.systemid) as six on six.systemid = one.systemid " +
						"left join (select monthSum.systemid,(isnull(leavNo.num1,0)*1.0/(thisSum + onSum))*2 as seven, isnull(leavNo.num1,0) sevenLeaveNo, (thisSum + onSum) as sevenSum from (select substring(systemid,1,4) as systemid,count(*) as onSum from bcode_job a inner join bcode b on b.bcode = a.bcode where isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-07') and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + year + "' + '-06' and CONVERT(varchar(7), entrydate, 120) <= '" + year + "' + '-06' group by substring(systemid,1,4)) as monthSum inner join (select substring(systemid,1,4) as systemid,count(*) as thisSum from bcode_job a inner join bcode b on b.bcode = a.bcode where isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-07') and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + year + "' + '-07' and CONVERT(varchar(7), entrydate, 120) <= '" + year + "' + '-07' group by substring(systemid,1,4)) as thisMonthSum on thisMonthSum.systemid = monthSum.systemid left join (select substring(systemid,1,4) as systemid,count(*) as num1 from bcode_job a inner join bcode b on b.bcode = a.bcode where isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-07') and CONVERT(varchar(7), lastjobdate, 120) = '" + year + "' + '-07' group by substring(systemid,1,4)) as leavNo on leavNo.systemid = monthSum.systemid) as seven on seven.systemid = one.systemid " +
						"left join (select monthSum.systemid,(isnull(leavNo.num1,0)*1.0/(thisSum + onSum))*2 as eight, isnull(leavNo.num1,0) eightLeaveNo, (thisSum + onSum) as eightSum from (select substring(systemid,1,4) as systemid,count(*) as onSum from bcode_job a inner join bcode b on b.bcode = a.bcode where isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-08') and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + year + "' + '-07' and CONVERT(varchar(7), entrydate, 120) <= '" + year + "' + '-07' group by substring(systemid,1,4)) as monthSum inner join (select substring(systemid,1,4) as systemid,count(*) as thisSum from bcode_job a inner join bcode b on b.bcode = a.bcode where isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-08') and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + year + "' + '-08' and CONVERT(varchar(7), entrydate, 120) <= '" + year + "' + '-08' group by substring(systemid,1,4)) as thisMonthSum on thisMonthSum.systemid = monthSum.systemid left join (select substring(systemid,1,4) as systemid,count(*) as num1 from bcode_job a inner join bcode b on b.bcode = a.bcode where isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-08') and CONVERT(varchar(7), lastjobdate, 120) = '" + year + "' + '-08' group by substring(systemid,1,4)) as leavNo on leavNo.systemid = monthSum.systemid) as eight on eight.systemid = one.systemid " +
						"left join (select monthSum.systemid,(isnull(leavNo.num1,0)*1.0/(thisSum + onSum))*2 as nine, isnull(leavNo.num1,0) nineLeaveNo, (thisSum + onSum) as nineSum from (select substring(systemid,1,4) as systemid,count(*) as onSum from bcode_job a inner join bcode b on b.bcode = a.bcode where isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-09') and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + year + "' + '-08' and CONVERT(varchar(7), entrydate, 120) <= '" + year + "' + '-08' group by substring(systemid,1,4)) as monthSum inner join (select substring(systemid,1,4) as systemid,count(*) as thisSum from bcode_job a inner join bcode b on b.bcode = a.bcode where isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-09') and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + year + "' + '-09' and CONVERT(varchar(7), entrydate, 120) <= '" + year + "' + '-09' group by substring(systemid,1,4)) as thisMonthSum on thisMonthSum.systemid = monthSum.systemid left join (select substring(systemid,1,4) as systemid,count(*) as num1 from bcode_job a inner join bcode b on b.bcode = a.bcode where isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-09') and CONVERT(varchar(7), lastjobdate, 120) = '" + year + "' + '-09' group by substring(systemid,1,4)) as leavNo on leavNo.systemid = monthSum.systemid) as nine on nine.systemid = one.systemid " +
						"left join (select monthSum.systemid,(isnull(leavNo.num1,0)*1.0/(thisSum + onSum))*2 as ten, isnull(leavNo.num1,0) tenLeaveNo, (thisSum + onSum) as tenSum from (select substring(systemid,1,4) as systemid,count(*) as onSum from bcode_job a inner join bcode b on b.bcode = a.bcode where isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-10') and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + year + "' + '-09' and CONVERT(varchar(7), entrydate, 120) <= '" + year + "' + '-09' group by substring(systemid,1,4)) as monthSum inner join (select substring(systemid,1,4) as systemid,count(*) as thisSum from bcode_job a inner join bcode b on b.bcode = a.bcode where isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-10') and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + year + "' + '-10' and CONVERT(varchar(7), entrydate, 120) <= '" + year + "' + '-10' group by substring(systemid,1,4)) as thisMonthSum on thisMonthSum.systemid = monthSum.systemid left join (select substring(systemid,1,4) as systemid,count(*) as num1 from bcode_job a inner join bcode b on b.bcode = a.bcode where isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-10') and CONVERT(varchar(7), lastjobdate, 120) = '" + year + "' + '-10' group by substring(systemid,1,4)) as leavNo on leavNo.systemid = monthSum.systemid) as ten on ten.systemid = one.systemid " +
						"left join (select monthSum.systemid,(isnull(leavNo.num1,0)*1.0/(thisSum + onSum))*2 as eleven, isnull(leavNo.num1,0) elevenLeaveNo, (thisSum + onSum) as elevenSum from (select substring(systemid,1,4) as systemid,count(*) as onSum from bcode_job a inner join bcode b on b.bcode = a.bcode where isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-11') and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + year + "' + '-10' and CONVERT(varchar(7), entrydate, 120) <= '" + year + "' + '-10' group by substring(systemid,1,4)) as monthSum inner join (select substring(systemid,1,4) as systemid,count(*) as thisSum from bcode_job a inner join bcode b on b.bcode = a.bcode where isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-11') and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + year + "' + '-11' and CONVERT(varchar(7), entrydate, 120) <= '" + year + "' + '-11' group by substring(systemid,1,4)) as thisMonthSum on thisMonthSum.systemid = monthSum.systemid left join (select substring(systemid,1,4) as systemid,count(*) as num1 from bcode_job a inner join bcode b on b.bcode = a.bcode where isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-11') and CONVERT(varchar(7), lastjobdate, 120) = '" + year + "' + '-11' group by substring(systemid,1,4)) as leavNo on leavNo.systemid = monthSum.systemid) as eleven on eleven.systemid = one.systemid " +
						"left join (select monthSum.systemid,(isnull(leavNo.num1,0)*1.0/(thisSum + onSum))*2 as twelve, isnull(leavNo.num1,0) twelveLeaveNo, (thisSum + onSum) as twelveSum from (select substring(systemid,1,4) as systemid,count(*) as onSum from bcode_job a inner join bcode b on b.bcode = a.bcode where isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-12') and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + year + "' + '-11' and CONVERT(varchar(7), entrydate, 120) <= '" + year + "' + '-11' group by substring(systemid,1,4)) as monthSum inner join (select substring(systemid,1,4) as systemid,count(*) as thisSum from bcode_job a inner join bcode b on b.bcode = a.bcode where isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-12') and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + year + "' + '-12' and CONVERT(varchar(7), entrydate, 120) <= '" + year + "' + '-12' group by substring(systemid,1,4)) as thisMonthSum on thisMonthSum.systemid = monthSum.systemid left join (select substring(systemid,1,4) as systemid,count(*) as num1 from bcode_job a inner join bcode b on b.bcode = a.bcode where isnull(jobtype,'') != '实习生' and isnull(jobtype,'') != '临时工' and not exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-12') and CONVERT(varchar(7), lastjobdate, 120) = '" + year + "' + '-12' group by substring(systemid,1,4)) as leavNo on leavNo.systemid = monthSum.systemid) as twelve on twelve.systemid = one.systemid " +
						"left join (select name,id from systeminfo where len(id) = 4) as system on system.id = one.systemid";
	
	String internsSql = "select one,two,three,four,five,six,seven,eight,nine,ten,eleven,twelve,oneLeaveNo,oneSum,twoLeaveNo,twoSum,threeLeaveNo,threeSum,fourLeaveNo,fourSum,fiveLeaveNo,fiveSum,sixLeaveNo,sixSum,sevenLeaveNo,sevenSum,eightLeaveNo,eightSum,nineLeaveNo,nineSum,tenLeaveNo,tenSum,elevenLeaveNo,elevenSum,twelveLeaveNo,twelveSum " + 
						"from (select monthSum.systemid,case when thisSum + onSum = 0 then 0 else (isnull(leavNo.num1,0)*1.0/(thisSum + onSum))*2 end as one, isnull(leavNo.num1,0) oneLeaveNo, (thisSum + onSum) as oneSum from (select '实习生' as systemid,count(*) as onSum from bcode_job a inner join bcode b on b.bcode = a.bcode where (jobtype = '实习生' or exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-01')) and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > CONVERT(varchar(7), dateadd(year, -1, '" + year + "' + '-12-01'), 120) and CONVERT(varchar(7), entrydate, 120) <= CONVERT(varchar(7), dateadd(year, -1, '" + year + "' + '-12-01'), 120)  ) as monthSum inner join (select '实习生' as systemid,count(*) as thisSum from bcode_job a inner join bcode b on b.bcode = a.bcode where (jobtype = '实习生' or exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-01')) and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + year + "' + '-01' and CONVERT(varchar(7), entrydate, 120) <= '" + year + "' + '-01'  ) as thisMonthSum on thisMonthSum.systemid = monthSum.systemid left join (select '实习生' as systemid,count(*) as num1 from bcode_job a inner join bcode b on b.bcode = a.bcode where (jobtype = '实习生' or exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-01')) and CONVERT(varchar(7), lastjobdate, 120) = '" + year + "' + '-01'  ) as leavNo on leavNo.systemid = monthSum.systemid) as one " +
						"left join (select monthSum.systemid,case when thisSum + onSum = 0 then 0 else (isnull(leavNo.num1,0)*1.0/(thisSum + onSum))*2 end as two, isnull(leavNo.num1,0) twoLeaveNo, (thisSum + onSum) as twoSum from (select '实习生' as systemid,count(*) as onSum from bcode_job a inner join bcode b on b.bcode = a.bcode where (jobtype = '实习生' or exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-02')) and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + year + "' + '-01' and CONVERT(varchar(7), entrydate, 120) <= '" + year + "' + '-01'  ) as monthSum inner join (select '实习生' as systemid,count(*) as thisSum from bcode_job a inner join bcode b on b.bcode = a.bcode where (jobtype = '实习生' or exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-02')) and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + year + "' + '-02' and CONVERT(varchar(7), entrydate, 120) <= '" + year + "' + '-02'  ) as thisMonthSum on thisMonthSum.systemid = monthSum.systemid left join (select '实习生' as systemid,count(*) as num1 from bcode_job a inner join bcode b on b.bcode = a.bcode where (jobtype = '实习生' or exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-02')) and CONVERT(varchar(7), lastjobdate, 120) = '" + year + "' + '-02'  ) as leavNo on leavNo.systemid = monthSum.systemid) as two on two.systemid = one.systemid " +
						"left join (select monthSum.systemid,case when thisSum + onSum = 0 then 0 else (isnull(leavNo.num1,0)*1.0/(thisSum + onSum))*2 end as three, isnull(leavNo.num1,0) threeLeaveNo, (thisSum + onSum) as threeSum from (select '实习生' as systemid,count(*) as onSum from bcode_job a inner join bcode b on b.bcode = a.bcode where (jobtype = '实习生' or exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-03')) and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + year + "' + '-02' and CONVERT(varchar(7), entrydate, 120) <= '" + year + "' + '-02'  ) as monthSum inner join (select '实习生' as systemid,count(*) as thisSum from bcode_job a inner join bcode b on b.bcode = a.bcode where (jobtype = '实习生' or exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-03')) and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + year + "' + '-03' and CONVERT(varchar(7), entrydate, 120) <= '" + year + "' + '-03'  ) as thisMonthSum on thisMonthSum.systemid = monthSum.systemid left join (select '实习生' as systemid,count(*) as num1 from bcode_job a inner join bcode b on b.bcode = a.bcode where (jobtype = '实习生' or exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-03')) and CONVERT(varchar(7), lastjobdate, 120) = '" + year + "' + '-03'  ) as leavNo on leavNo.systemid = monthSum.systemid) as three on three.systemid = one.systemid " +
						"left join (select monthSum.systemid,case when thisSum + onSum = 0 then 0 else (isnull(leavNo.num1,0)*1.0/(thisSum + onSum))*2 end as four, isnull(leavNo.num1,0) fourLeaveNo, (thisSum + onSum) as fourSum from (select '实习生' as systemid,count(*) as onSum from bcode_job a inner join bcode b on b.bcode = a.bcode where (jobtype = '实习生' or exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-04')) and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + year + "' + '-03' and CONVERT(varchar(7), entrydate, 120) <= '" + year + "' + '-03'  ) as monthSum inner join (select '实习生' as systemid,count(*) as thisSum from bcode_job a inner join bcode b on b.bcode = a.bcode where (jobtype = '实习生' or exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-04')) and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + year + "' + '-04' and CONVERT(varchar(7), entrydate, 120) <= '" + year + "' + '-04'  ) as thisMonthSum on thisMonthSum.systemid = monthSum.systemid left join (select '实习生' as systemid,count(*) as num1 from bcode_job a inner join bcode b on b.bcode = a.bcode where (jobtype = '实习生' or exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-04')) and CONVERT(varchar(7), lastjobdate, 120) = '" + year + "' + '-04'  ) as leavNo on leavNo.systemid = monthSum.systemid) as four on four.systemid = one.systemid " +
						"left join (select monthSum.systemid,case when thisSum + onSum = 0 then 0 else (isnull(leavNo.num1,0)*1.0/(thisSum + onSum))*2 end as five, isnull(leavNo.num1,0) fiveLeaveNo, (thisSum + onSum) as fiveSum from (select '实习生' as systemid,count(*) as onSum from bcode_job a inner join bcode b on b.bcode = a.bcode where (jobtype = '实习生' or exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-05')) and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + year + "' + '-04' and CONVERT(varchar(7), entrydate, 120) <= '" + year + "' + '-04'  ) as monthSum inner join (select '实习生' as systemid,count(*) as thisSum from bcode_job a inner join bcode b on b.bcode = a.bcode where (jobtype = '实习生' or exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-05')) and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + year + "' + '-05' and CONVERT(varchar(7), entrydate, 120) <= '" + year + "' + '-05'  ) as thisMonthSum on thisMonthSum.systemid = monthSum.systemid left join (select '实习生' as systemid,count(*) as num1 from bcode_job a inner join bcode b on b.bcode = a.bcode where (jobtype = '实习生' or exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-05')) and CONVERT(varchar(7), lastjobdate, 120) = '" + year + "' + '-05'  ) as leavNo on leavNo.systemid = monthSum.systemid) as five on five.systemid = one.systemid " +
						"left join (select monthSum.systemid,case when thisSum + onSum = 0 then 0 else (isnull(leavNo.num1,0)*1.0/(thisSum + onSum))*2 end as six, isnull(leavNo.num1,0) sixLeaveNo, (thisSum + onSum) as sixSum from (select '实习生' as systemid,count(*) as onSum from bcode_job a inner join bcode b on b.bcode = a.bcode where (jobtype = '实习生' or exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-06')) and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + year + "' + '-05' and CONVERT(varchar(7), entrydate, 120) <= '" + year + "' + '-05'  ) as monthSum inner join (select '实习生' as systemid,count(*) as thisSum from bcode_job a inner join bcode b on b.bcode = a.bcode where (jobtype = '实习生' or exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-06')) and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + year + "' + '-06' and CONVERT(varchar(7), entrydate, 120) <= '" + year + "' + '-06'  ) as thisMonthSum on thisMonthSum.systemid = monthSum.systemid left join (select '实习生' as systemid,count(*) as num1 from bcode_job a inner join bcode b on b.bcode = a.bcode where (jobtype = '实习生' or exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-06')) and CONVERT(varchar(7), lastjobdate, 120) = '" + year + "' + '-06'  ) as leavNo on leavNo.systemid = monthSum.systemid) as six on six.systemid = one.systemid " +
						"left join (select monthSum.systemid,case when thisSum + onSum = 0 then 0 else (isnull(leavNo.num1,0)*1.0/(thisSum + onSum))*2 end as seven, isnull(leavNo.num1,0) sevenLeaveNo, (thisSum + onSum) as sevenSum from (select '实习生' as systemid,count(*) as onSum from bcode_job a inner join bcode b on b.bcode = a.bcode where (jobtype = '实习生' or exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-07')) and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + year + "' + '-06' and CONVERT(varchar(7), entrydate, 120) <= '" + year + "' + '-06'  ) as monthSum inner join (select '实习生' as systemid,count(*) as thisSum from bcode_job a inner join bcode b on b.bcode = a.bcode where (jobtype = '实习生' or exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-07')) and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + year + "' + '-07' and CONVERT(varchar(7), entrydate, 120) <= '" + year + "' + '-07'  ) as thisMonthSum on thisMonthSum.systemid = monthSum.systemid left join (select '实习生' as systemid,count(*) as num1 from bcode_job a inner join bcode b on b.bcode = a.bcode where (jobtype = '实习生' or exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-07')) and CONVERT(varchar(7), lastjobdate, 120) = '" + year + "' + '-07'  ) as leavNo on leavNo.systemid = monthSum.systemid) as seven on seven.systemid = one.systemid " +
						"left join (select monthSum.systemid,case when thisSum + onSum = 0 then 0 else (isnull(leavNo.num1,0)*1.0/(thisSum + onSum))*2 end as eight, isnull(leavNo.num1,0) eightLeaveNo, (thisSum + onSum) as eightSum from (select '实习生' as systemid,count(*) as onSum from bcode_job a inner join bcode b on b.bcode = a.bcode where (jobtype = '实习生' or exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-08')) and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + year + "' + '-07' and CONVERT(varchar(7), entrydate, 120) <= '" + year + "' + '-07'  ) as monthSum inner join (select '实习生' as systemid,count(*) as thisSum from bcode_job a inner join bcode b on b.bcode = a.bcode where (jobtype = '实习生' or exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-08')) and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + year + "' + '-08' and CONVERT(varchar(7), entrydate, 120) <= '" + year + "' + '-08'  ) as thisMonthSum on thisMonthSum.systemid = monthSum.systemid left join (select '实习生' as systemid,count(*) as num1 from bcode_job a inner join bcode b on b.bcode = a.bcode where (jobtype = '实习生' or exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-08')) and CONVERT(varchar(7), lastjobdate, 120) = '" + year + "' + '-08'  ) as leavNo on leavNo.systemid = monthSum.systemid) as eight on eight.systemid = one.systemid " +
						"left join (select monthSum.systemid,case when thisSum + onSum = 0 then 0 else (isnull(leavNo.num1,0)*1.0/(thisSum + onSum))*2 end as nine, isnull(leavNo.num1,0) nineLeaveNo, (thisSum + onSum) as nineSum from (select '实习生' as systemid,count(*) as onSum from bcode_job a inner join bcode b on b.bcode = a.bcode where (jobtype = '实习生' or exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-09')) and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + year + "' + '-08' and CONVERT(varchar(7), entrydate, 120) <= '" + year + "' + '-08'  ) as monthSum inner join (select '实习生' as systemid,count(*) as thisSum from bcode_job a inner join bcode b on b.bcode = a.bcode where (jobtype = '实习生' or exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-09')) and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + year + "' + '-09' and CONVERT(varchar(7), entrydate, 120) <= '" + year + "' + '-09'  ) as thisMonthSum on thisMonthSum.systemid = monthSum.systemid left join (select '实习生' as systemid,count(*) as num1 from bcode_job a inner join bcode b on b.bcode = a.bcode where (jobtype = '实习生' or exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-09')) and CONVERT(varchar(7), lastjobdate, 120) = '" + year + "' + '-09'  ) as leavNo on leavNo.systemid = monthSum.systemid) as nine on nine.systemid = one.systemid " +
						"left join (select monthSum.systemid,case when thisSum + onSum = 0 then 0 else (isnull(leavNo.num1,0)*1.0/(thisSum + onSum))*2 end as ten, isnull(leavNo.num1,0) tenLeaveNo, (thisSum + onSum) as tenSum from (select '实习生' as systemid,count(*) as onSum from bcode_job a inner join bcode b on b.bcode = a.bcode where (jobtype = '实习生' or exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-10')) and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + year + "' + '-09' and CONVERT(varchar(7), entrydate, 120) <= '" + year + "' + '-09'  ) as monthSum inner join (select '实习生' as systemid,count(*) as thisSum from bcode_job a inner join bcode b on b.bcode = a.bcode where (jobtype = '实习生' or exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-10')) and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + year + "' + '-10' and CONVERT(varchar(7), entrydate, 120) <= '" + year + "' + '-10'  ) as thisMonthSum on thisMonthSum.systemid = monthSum.systemid left join (select '实习生' as systemid,count(*) as num1 from bcode_job a inner join bcode b on b.bcode = a.bcode where (jobtype = '实习生' or exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-10')) and CONVERT(varchar(7), lastjobdate, 120) = '" + year + "' + '-10'  ) as leavNo on leavNo.systemid = monthSum.systemid) as ten on ten.systemid = one.systemid " +
						"left join (select monthSum.systemid,case when thisSum + onSum = 0 then 0 else (isnull(leavNo.num1,0)*1.0/(thisSum + onSum))*2 end as eleven, isnull(leavNo.num1,0) elevenLeaveNo, (thisSum + onSum) as elevenSum from (select '实习生' as systemid,count(*) as onSum from bcode_job a inner join bcode b on b.bcode = a.bcode where (jobtype = '实习生' or exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-11')) and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + year + "' + '-10' and CONVERT(varchar(7), entrydate, 120) <= '" + year + "' + '-10'  ) as monthSum inner join (select '实习生' as systemid,count(*) as thisSum from bcode_job a inner join bcode b on b.bcode = a.bcode where (jobtype = '实习生' or exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-11')) and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + year + "' + '-11' and CONVERT(varchar(7), entrydate, 120) <= '" + year + "' + '-11'  ) as thisMonthSum on thisMonthSum.systemid = monthSum.systemid left join (select '实习生' as systemid,count(*) as num1 from bcode_job a inner join bcode b on b.bcode = a.bcode where (jobtype = '实习生' or exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-11')) and CONVERT(varchar(7), lastjobdate, 120) = '" + year + "' + '-11'  ) as leavNo on leavNo.systemid = monthSum.systemid) as eleven on eleven.systemid = one.systemid " +
						"left join (select monthSum.systemid,case when thisSum + onSum = 0 then 0 else (isnull(leavNo.num1,0)*1.0/(thisSum + onSum))*2 end as twelve, isnull(leavNo.num1,0) twelveLeaveNo, (thisSum + onSum) as twelveSum from (select '实习生' as systemid,count(*) as onSum from bcode_job a inner join bcode b on b.bcode = a.bcode where (jobtype = '实习生' or exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-12')) and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + year + "' + '-11' and CONVERT(varchar(7), entrydate, 120) <= '" + year + "' + '-11'  ) as monthSum inner join (select '实习生' as systemid,count(*) as thisSum from bcode_job a inner join bcode b on b.bcode = a.bcode where (jobtype = '实习生' or exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-12')) and isnull(CONVERT(varchar(7), lastjobdate, 120),'2050-08') > '" + year + "' + '-12' and CONVERT(varchar(7), entrydate, 120) <= '" + year + "' + '-12'  ) as thisMonthSum on thisMonthSum.systemid = monthSum.systemid left join (select '实习生' as systemid,count(*) as num1 from bcode_job a inner join bcode b on b.bcode = a.bcode where (jobtype = '实习生' or exists(select * from hr_EmployeeChange where ChangeType = '转正' and before = '实习生' and bcode = a.bcode and convert(varchar(7),ChangeDate,120) > '" + year + "' + '-12')) and CONVERT(varchar(7), lastjobdate, 120) = '" + year + "' + '-12'  ) as leavNo on leavNo.systemid = monthSum.systemid) as twelve on twelve.systemid = one.systemid";
	DataSet depLeaving =DST.getDataSet(depSql);
	DataSet internsLeaving = DST.getDataSet(internsSql);
	
	int oneSum = 0;  
	int twoSum = 0;  
	int threeSum = 0;  
	int fourSum = 0;  
	int fiveSum = 0;  
	int sixSum = 0;  
	int sevenSum = 0;  
	int eightSum = 0;  
	int nineSum = 0;  
	int tenSum = 0;  
	int elevenSum = 0;  
	int twelveSum = 0; 
	
	int oneLeaveNo = 0;  
	int twoLeaveNo = 0;  
	int threeLeaveNo = 0;  
	int fourLeaveNo = 0;  
	int fiveLeaveNo = 0;  
	int sixLeaveNo = 0;  
	int sevenLeaveNo = 0;  
	int eightLeaveNo = 0;  
	int nineLeaveNo = 0;  
	int tenLeaveNo = 0;  
	int elevenLeaveNo = 0;  
	int twelveLeaveNo = 0; 
%>
<div id="40percentage">
<%= year %>年部门流失率统计<br/>
  <table width="100%" style="border-collapse: collapse" border="1" cellspacing="0" cellpadding="3" bordercolordark="#CCCCCC" bordercolorlight="#CCCCCC" style="line-height: 120%;border-color:#000000;font-size:10px">
    <tr bgcolor="#e8e8e0" align="center">
      <td>部门</td>
      <td>一月</td>
      <td>二月</td>
      <td>三月</td>
      <td>四月</td>
      <td>五月</td>
      <td>六月</td>
      <td>七月</td>
      <td>八月</td>
      <td>九月</td>
      <td>十月</td>
      <td>十一月</td>
      <td>十二月</td>
    </tr>
<%
	for(int i=0; i<depLeaving.getRowCount(); i++) {
		double f1 = Double.parseDouble(depLeaving.getValue(i, "one")) * 100;
		double f2 = Double.parseDouble(depLeaving.getValue(i, "two")) * 100;
		double f3 = Double.parseDouble(depLeaving.getValue(i, "three")) * 100;
		double f4 = Double.parseDouble(depLeaving.getValue(i, "four")) * 100;
		double f5 = Double.parseDouble(depLeaving.getValue(i, "five")) * 100;
		double f6 = Double.parseDouble(depLeaving.getValue(i, "six")) * 100;
		double f7 = Double.parseDouble(depLeaving.getValue(i, "seven")) * 100;
		double f8 = Double.parseDouble(depLeaving.getValue(i, "eight")) * 100;
		double f9 = Double.parseDouble(depLeaving.getValue(i, "nine")) * 100;
		double f10 = Double.parseDouble(depLeaving.getValue(i, "ten")) * 100;
		double f11 = Double.parseDouble(depLeaving.getValue(i, "eleven")) * 100;
		double f12 = Double.parseDouble(depLeaving.getValue(i, "twelve")) * 100;
		
		BigDecimal one = new BigDecimal(f1).setScale(2,BigDecimal.ROUND_HALF_UP);
		BigDecimal two = new BigDecimal(f2).setScale(2,BigDecimal.ROUND_HALF_UP);
		BigDecimal three = new BigDecimal(f3).setScale(2,BigDecimal.ROUND_HALF_UP);
		BigDecimal four = new BigDecimal(f4).setScale(2,BigDecimal.ROUND_HALF_UP);
		BigDecimal five = new BigDecimal(f5).setScale(2,BigDecimal.ROUND_HALF_UP);
		BigDecimal six = new BigDecimal(f6).setScale(2,BigDecimal.ROUND_HALF_UP);
		BigDecimal seven = new BigDecimal(f7).setScale(2,BigDecimal.ROUND_HALF_UP);
		BigDecimal eight = new BigDecimal(f8).setScale(2,BigDecimal.ROUND_HALF_UP);
		BigDecimal nine = new BigDecimal(f9).setScale(2,BigDecimal.ROUND_HALF_UP);
		BigDecimal ten = new BigDecimal(f10).setScale(2,BigDecimal.ROUND_HALF_UP);
		BigDecimal eleven = new BigDecimal(f11).setScale(2,BigDecimal.ROUND_HALF_UP);
		BigDecimal twelve = new BigDecimal(f12).setScale(2,BigDecimal.ROUND_HALF_UP);
		
		oneSum = oneSum + Integer.parseInt(depLeaving.getValue(i, "oneSum"));
		twoSum = twoSum + Integer.parseInt(depLeaving.getValue(i, "twoSum"));
		threeSum = threeSum + Integer.parseInt(depLeaving.getValue(i, "threeSum"));
		fourSum = fourSum + Integer.parseInt(depLeaving.getValue(i, "fourSum"));
		fiveSum = fiveSum + Integer.parseInt(depLeaving.getValue(i, "fiveSum"));
		sixSum = sixSum + Integer.parseInt(depLeaving.getValue(i, "sixSum"));
		sevenSum = sevenSum + Integer.parseInt(depLeaving.getValue(i, "sevenSum"));
		eightSum = eightSum + Integer.parseInt(depLeaving.getValue(i, "eightSum"));
		nineSum = nineSum + Integer.parseInt(depLeaving.getValue(i, "nineSum"));
		tenSum = tenSum + Integer.parseInt(depLeaving.getValue(i, "tenSum"));
		elevenSum = elevenSum + Integer.parseInt(depLeaving.getValue(i, "elevenSum"));
		twelveSum = twelveSum + Integer.parseInt(depLeaving.getValue(i, "twelveSum"));
		
		oneLeaveNo = oneLeaveNo + Integer.parseInt(depLeaving.getValue(i, "oneLeaveNo"));
		twoLeaveNo = twoLeaveNo + Integer.parseInt(depLeaving.getValue(i, "twoLeaveNo"));
		threeLeaveNo = threeLeaveNo + Integer.parseInt(depLeaving.getValue(i, "threeLeaveNo"));
		fourLeaveNo = fourLeaveNo + Integer.parseInt(depLeaving.getValue(i, "fourLeaveNo"));
		fiveLeaveNo = fiveLeaveNo + Integer.parseInt(depLeaving.getValue(i, "fiveLeaveNo"));
		sixLeaveNo = sixLeaveNo + Integer.parseInt(depLeaving.getValue(i, "sixLeaveNo"));
		sevenLeaveNo = sevenLeaveNo + Integer.parseInt(depLeaving.getValue(i, "sevenLeaveNo"));
		eightLeaveNo = eightLeaveNo + Integer.parseInt(depLeaving.getValue(i, "eightLeaveNo"));
		nineLeaveNo = nineLeaveNo + Integer.parseInt(depLeaving.getValue(i, "nineLeaveNo"));
		tenLeaveNo = tenLeaveNo + Integer.parseInt(depLeaving.getValue(i, "tenLeaveNo"));
		elevenLeaveNo = elevenLeaveNo + Integer.parseInt(depLeaving.getValue(i, "elevenLeaveNo"));
		twelveLeaveNo = twelveLeaveNo + Integer.parseInt(depLeaving.getValue(i, "twelveLeaveNo"));
%>
	<tr>
	  <td><%= depLeaving.getValue(i, "name") %></td>
      <td><%= one + "%" %></td>
      <td><%= two + "%" %></td>
      <td><%= three + "%" %></td>
      <td><%= four + "%" %></td>
      <td><%= five + "%" %></td>
      <td><%= six + "%" %></td>
      <td><%= seven + "%" %></td>
      <td><%= eight + "%" %></td>
      <td><%= nine + "%" %></td>
      <td><%= ten + "%" %></td>
      <td><%= eleven + "%" %></td>
      <td><%= twelve + "%" %></td>
    </tr>
<%
}
BigDecimal one = new BigDecimal(oneLeaveNo*2.0/oneSum*100).setScale(2,BigDecimal.ROUND_HALF_UP);
BigDecimal two = new BigDecimal(twoLeaveNo*2.0/twoSum*100).setScale(2,BigDecimal.ROUND_HALF_UP);
BigDecimal three = new BigDecimal(threeLeaveNo*2.0/threeSum*100).setScale(2,BigDecimal.ROUND_HALF_UP);
BigDecimal four = new BigDecimal(fourLeaveNo*2.0/fourSum*100).setScale(2,BigDecimal.ROUND_HALF_UP);
BigDecimal five = new BigDecimal(fiveLeaveNo*2.0/fiveSum*100).setScale(2,BigDecimal.ROUND_HALF_UP);
BigDecimal six = new BigDecimal(sixLeaveNo*2.0/sixSum*100).setScale(2,BigDecimal.ROUND_HALF_UP);
BigDecimal seven = new BigDecimal(sevenLeaveNo*2.0/sevenSum*100).setScale(2,BigDecimal.ROUND_HALF_UP);
BigDecimal eight = new BigDecimal(eightLeaveNo*2.0/eightSum*100).setScale(2,BigDecimal.ROUND_HALF_UP);
BigDecimal nine = new BigDecimal(nineLeaveNo*2.0/nineSum*100).setScale(2,BigDecimal.ROUND_HALF_UP);
BigDecimal ten = new BigDecimal(tenLeaveNo*2.0/tenSum*100).setScale(2,BigDecimal.ROUND_HALF_UP);
BigDecimal eleven = new BigDecimal(elevenLeaveNo*2.0/elevenSum*100).setScale(2,BigDecimal.ROUND_HALF_UP);
BigDecimal twelve = new BigDecimal(twelveLeaveNo*2.0/twelveSum*100).setScale(2,BigDecimal.ROUND_HALF_UP);
%>
	<tr>
	  <td>总计（不计实习生）</td>
      <td><%= one + "%" %></td>
      <td><%= two + "%" %></td>
      <td><%= three + "%" %></td>
      <td><%= four + "%" %></td>
      <td><%= five + "%" %></td>
      <td><%= six + "%" %></td>
      <td><%= seven + "%" %></td>
      <td><%= eight + "%" %></td>
      <td><%= nine + "%" %></td>
      <td><%= ten + "%" %></td>
      <td><%= eleven + "%" %></td>
      <td><%= twelve + "%" %></td>
    </tr>

<%
double f1 = Double.parseDouble(internsLeaving.getValue(0, "one")) * 100;
double f2 = Double.parseDouble(internsLeaving.getValue(0, "two")) * 100;
double f3 = Double.parseDouble(internsLeaving.getValue(0, "three")) * 100;
double f4 = Double.parseDouble(internsLeaving.getValue(0, "four")) * 100;
double f5 = Double.parseDouble(internsLeaving.getValue(0, "five")) * 100;
double f6 = Double.parseDouble(internsLeaving.getValue(0, "six")) * 100;
double f7 = Double.parseDouble(internsLeaving.getValue(0, "seven")) * 100;
double f8 = Double.parseDouble(internsLeaving.getValue(0, "eight")) * 100;
double f9 = Double.parseDouble(internsLeaving.getValue(0, "nine")) * 100;
double f10 = Double.parseDouble(internsLeaving.getValue(0, "ten")) * 100;
double f11 = Double.parseDouble(internsLeaving.getValue(0, "eleven")) * 100;
double f12 = Double.parseDouble(internsLeaving.getValue(0, "twelve")) * 100;

BigDecimal oneInterns = new BigDecimal(f1).setScale(2,BigDecimal.ROUND_HALF_UP);
BigDecimal twoInterns = new BigDecimal(f2).setScale(2,BigDecimal.ROUND_HALF_UP);
BigDecimal threeInterns = new BigDecimal(f3).setScale(2,BigDecimal.ROUND_HALF_UP);
BigDecimal fourInterns = new BigDecimal(f4).setScale(2,BigDecimal.ROUND_HALF_UP);
BigDecimal fiveInterns = new BigDecimal(f5).setScale(2,BigDecimal.ROUND_HALF_UP);
BigDecimal sixInterns = new BigDecimal(f6).setScale(2,BigDecimal.ROUND_HALF_UP);
BigDecimal sevenInterns = new BigDecimal(f7).setScale(2,BigDecimal.ROUND_HALF_UP);
BigDecimal eightInterns = new BigDecimal(f8).setScale(2,BigDecimal.ROUND_HALF_UP);
BigDecimal nineInterns = new BigDecimal(f9).setScale(2,BigDecimal.ROUND_HALF_UP);
BigDecimal tenInterns = new BigDecimal(f10).setScale(2,BigDecimal.ROUND_HALF_UP);
BigDecimal elevenInterns = new BigDecimal(f11).setScale(2,BigDecimal.ROUND_HALF_UP);
BigDecimal twelveInterns = new BigDecimal(f12).setScale(2,BigDecimal.ROUND_HALF_UP);

oneSum = oneSum + Integer.parseInt(internsLeaving.getValue(0, "oneSum"));
twoSum = twoSum + Integer.parseInt(internsLeaving.getValue(0, "twoSum"));
threeSum = threeSum + Integer.parseInt(internsLeaving.getValue(0, "threeSum"));
fourSum = fourSum + Integer.parseInt(internsLeaving.getValue(0, "fourSum"));
fiveSum = fiveSum + Integer.parseInt(internsLeaving.getValue(0, "fiveSum"));
sixSum = sixSum + Integer.parseInt(internsLeaving.getValue(0, "sixSum"));
sevenSum = sevenSum + Integer.parseInt(internsLeaving.getValue(0, "sevenSum"));
eightSum = eightSum + Integer.parseInt(internsLeaving.getValue(0, "eightSum"));
nineSum = nineSum + Integer.parseInt(internsLeaving.getValue(0, "nineSum"));
tenSum = tenSum + Integer.parseInt(internsLeaving.getValue(0, "tenSum"));
elevenSum = elevenSum + Integer.parseInt(internsLeaving.getValue(0, "elevenSum"));
twelveSum = twelveSum + Integer.parseInt(internsLeaving.getValue(0, "twelveSum"));

oneLeaveNo = oneLeaveNo + Integer.parseInt(internsLeaving.getValue(0, "oneLeaveNo"));
twoLeaveNo = twoLeaveNo + Integer.parseInt(internsLeaving.getValue(0, "twoLeaveNo"));
threeLeaveNo = threeLeaveNo + Integer.parseInt(internsLeaving.getValue(0, "threeLeaveNo"));
fourLeaveNo = fourLeaveNo + Integer.parseInt(internsLeaving.getValue(0, "fourLeaveNo"));
fiveLeaveNo = fiveLeaveNo + Integer.parseInt(internsLeaving.getValue(0, "fiveLeaveNo"));
sixLeaveNo = sixLeaveNo + Integer.parseInt(internsLeaving.getValue(0, "sixLeaveNo"));
sevenLeaveNo = sevenLeaveNo + Integer.parseInt(internsLeaving.getValue(0, "sevenLeaveNo"));
eightLeaveNo = eightLeaveNo + Integer.parseInt(internsLeaving.getValue(0, "eightLeaveNo"));
nineLeaveNo = nineLeaveNo + Integer.parseInt(internsLeaving.getValue(0, "nineLeaveNo"));
tenLeaveNo = tenLeaveNo + Integer.parseInt(internsLeaving.getValue(0, "tenLeaveNo"));
elevenLeaveNo = elevenLeaveNo + Integer.parseInt(internsLeaving.getValue(0, "elevenLeaveNo"));
twelveLeaveNo = twelveLeaveNo + Integer.parseInt(internsLeaving.getValue(0, "twelveLeaveNo"));


one = new BigDecimal(oneLeaveNo*2.0/oneSum*100).setScale(2,BigDecimal.ROUND_HALF_UP);
two = new BigDecimal(twoLeaveNo*2.0/twoSum*100).setScale(2,BigDecimal.ROUND_HALF_UP);
three = new BigDecimal(threeLeaveNo*2.0/threeSum*100).setScale(2,BigDecimal.ROUND_HALF_UP);
four = new BigDecimal(fourLeaveNo*2.0/fourSum*100).setScale(2,BigDecimal.ROUND_HALF_UP);
five = new BigDecimal(fiveLeaveNo*2.0/fiveSum*100).setScale(2,BigDecimal.ROUND_HALF_UP);
six = new BigDecimal(sixLeaveNo*2.0/sixSum*100).setScale(2,BigDecimal.ROUND_HALF_UP);
seven = new BigDecimal(sevenLeaveNo*2.0/sevenSum*100).setScale(2,BigDecimal.ROUND_HALF_UP);
eight = new BigDecimal(eightLeaveNo*2.0/eightSum*100).setScale(2,BigDecimal.ROUND_HALF_UP);
nine = new BigDecimal(nineLeaveNo*2.0/nineSum*100).setScale(2,BigDecimal.ROUND_HALF_UP);
ten = new BigDecimal(tenLeaveNo*2.0/tenSum*100).setScale(2,BigDecimal.ROUND_HALF_UP);
eleven = new BigDecimal(elevenLeaveNo*2.0/elevenSum*100).setScale(2,BigDecimal.ROUND_HALF_UP);
twelve = new BigDecimal(twelveLeaveNo*2.0/twelveSum*100).setScale(2,BigDecimal.ROUND_HALF_UP);
%>
    <tr>
	  <td>实习生</td>
      <td><%= oneInterns + "%" %></td>
      <td><%= twoInterns + "%" %></td>
      <td><%= threeInterns + "%" %></td>
      <td><%= fourInterns + "%" %></td>
      <td><%= fiveInterns + "%" %></td>
      <td><%= sixInterns + "%" %></td>
      <td><%= sevenInterns + "%" %></td>
      <td><%= eightInterns + "%" %></td>
      <td><%= nineInterns + "%" %></td>
      <td><%= tenInterns + "%" %></td>
      <td><%= elevenInterns + "%" %></td>
      <td><%= twelveInterns + "%" %></td>
    </tr>
    
    <tr>
	  <td>总计（包括实习生）</td>
      <td><%= one + "%" %></td>
      <td><%= two + "%" %></td>
      <td><%= three + "%" %></td>
      <td><%= four + "%" %></td>
      <td><%= five + "%" %></td>
      <td><%= six + "%" %></td>
      <td><%= seven + "%" %></td>
      <td><%= eight + "%" %></td>
      <td><%= nine + "%" %></td>
      <td><%= ten + "%" %></td>
      <td><%= eleven + "%" %></td>
      <td><%= twelve + "%" %></td>
    </tr>
  </table>
</div>
</body>
</html>
